﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LTSMZ.life.Model;
using Qianli.DirSir.Common;
using System.Data.SqlClient;
using System.Data;

namespace LTSMZ.life.DAL
{
    public class ProductDal
    {
        SQLHelper SqlHelper = new SQLHelper();
        /// <summary>
        /// 商品信息添加
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        public int AddProduct(Product p)
        {
            //string str1 = "insert into Product values('" + p.Pname + "',0," + p.Price + ",'" + p.Specification + "','" + p.Explain + "','" + p.Place + "'," + p.ProState + "," + p.Sid + "," + p.CPrice + ",'"+p.PPname+"','"+p.PTrait+"')";
            string str = "insert into Product values('" + p.Pname + "',0," + p.Price + ",'" + p.Specification + "','" + p.Explain + "','" + p.Place + "'," + p.ProState + "," + p.Sid + "," + p.CPrice + ",'" + p.PPname + "','" + p.PTrait + "','" + p.AlikeProduct + "','" + p.GoodRate + "','" + p.serverG + "')";
            return SqlHelper.ExecNonquery(str);
        }
        /// <summary>
        /// 商品信息删除
        /// </summary>
        /// <returns></returns>
        public int DeleteProductById(int id)
        {
            string str = "delete from Product where Pid=" + id + "";
            return SqlHelper.ExecNonquery(str);
        }
        /// <summary>
        /// 商品信息(根据编号查找该信息）
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public SqlDataReader ShowProductBypid(int id)
        {
            string str = "select * from Product where Pid=" + id + "";
            return SqlHelper.ExecReader(str);
        }
        /// <summary>
        /// 下拉框显示
        /// </summary>
        /// <returns></returns>
        public List<SecondType> ShowSecondType()
        {
            string sql = "select SecondType.Sid,SecondType.Sname from SecondType ";
            DataTable dt = SqlHelper.ExecDataTable(sql);
            List<SecondType> list = new List<Model.SecondType>();
            foreach (DataRow row in dt.Rows)
            {
                SecondType pla = new SecondType();
                pla.Sid = Convert.ToInt32(row["Sid"]);
                pla.Sname = row["Sname"].ToString();
                list.Add(pla);
            }

            return list;
        }
        //public List<SecondType> ShowSecondType()
        //{
        //    SqlConnection con = new SqlConnection("Data Source=192.168.0.47;Initial Catalog=lift;User ID=sa;pwd=123123");
        //    string sql = "SELECT Sid, Sname FROM SecondType ";
        //    SqlDataAdapter adapter = new SqlDataAdapter(sql, con);
        //    DataTable table = new DataTable();
        //    adapter.Fill(table);

        //    List<SecondType> list = new List<Model.SecondType>();
        //    foreach (DataRow row in table.Rows)
        //    {
        //        SecondType pla = new SecondType();
        //        pla.Sid = Convert.ToInt32(row["Sid"]);
        //        pla.Sname = row["Sname"].ToString();

        //        list.Add(pla);
        //    }

        //    return list;
        //}


        ///// <summary>
        ///// 商品信息修改（修改）
        ///// </summary>
        ///// <param name="w"></param>
        ///// <returns></returns>
        //public int AlterProduct(Product p)
        //{
        //    //string str = "update Product set Pname='" + p.Pname + "',pnum=" + p.Pnum + ",Price=" + p.Price + ",Specification=" + p.Specification + ",Explain='" + p.Explain + "',Place='" + p.Place + "',ProState=" + p.ProState + ",Sid=" + p.Sid + ",PImg='" + p.PImg + "',CPrice=" + p.CPrice + " where Pid=" + p.Pid + "";
        //    return SqlHelper.ExecNonquery_Proc("P_AddProduct");
        //}

        /// <summary>
        /// 商品信息修改（修改）
        /// </summary>
        /// <param name="w"></param>
        /// <returns></returns>
        public int AlterProduct(Product p)
        {
            string str = "update Product set Pname='" + p.Pname + "',pnum=" + p.Pnum + ",Price=" + p.Price + ",Specification=" + p.Specification + ",Explain='" + p.Explain + "',Place='" + p.Place + "',ProState=" + p.ProState + ",Sid=" + p.Sid + ",CPrice=" + p.CPrice + ",PPname=" + p.PPname + ",PTrait=" + p.PTrait + ",AlikeProduct=" + p.AlikeProduct + ",GoodRate=" + p.GoodRate + ",serverG=" + p.serverG + " where Pid=" + p.Pid + "";
            return SqlHelper.ExecNonquery(str);
        }
        ///// <summary>
        ///// 商品信息显示
        ///// </summary>
        ///// <returns></returns>
        //public SqlDataReader show(string where)
        //{
        //    string str = "select * from Product where ProState =1 ";
        //    if (where != "")
        //    {
        //        str += "where " + where;
        //    }
        //    return SqlHelper.ExecReader(str);
        //}
        /// <summary>
        /// 商品信息显示
        /// </summary>
        /// <returns></returns>
        //public SqlDataReader show()
        //{
        //    string str = "select * from Product";
        //    return SqlHelper.ExecReader(str);
        //}
        /// <summary>
        /// 商品信息显示
        /// </summary>
        /// <returns></returns>
        public List<Product> show()
        {
            string sql = "select Product.Pid,Product.CPrice,Product.Pname,Stock.AvailableStocks,Product.Price,Product.Specification,Product.Explain,Product.Place,Product.ProState,SecondType.Sname,PImgHref from Product join Stock on Stock.Pid=Product.Pid join SecondType on SecondType.Sid=Product.Sid join Pimg on PImg.Pid=Product.Pid where Pzhu=1";
            DataTable dt = SqlHelper.ExecDataTable(sql);
            List<Product> list = new List<Product>();
            foreach (DataRow d in dt.Rows)
            {
                Product p = new Product();
                p.Pid = Convert.ToInt32(d["Pid"]);
                p.Pname = d["Pname"].ToString();
                p.AvailableStocks = Convert.ToInt32(d["AvailableStocks"]);
                p.Price = Convert.ToDecimal(d["Price"]);
                p.Specification = d["Specification"].ToString();
                p.PImgHref = d["PImgHref"].ToString();
                p.Explain = d["Explain"].ToString();
                p.Place = d["Place"].ToString();
                p.ProState = Convert.ToInt32(d["ProState"]);
                p.Sname = d["Sname"].ToString();
                p.CPrice = Convert.ToDecimal(d["CPrice"]);
                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 商品上下架
        /// </summary>
        /// <param name="Pid"></param>
        /// <returns></returns>
        public int AlterProduct1(int Pid)
        {
            string str = "select Product.ProState from Product where Pid=" + Pid + "";
            int i = 0;
            SqlDataReader reader = SqlHelper.ExecReader(str);
            while (reader.Read())
            {
                i = Convert.ToInt32(reader["ProState"]);
            }
            if (i == 0)
            {
                string sql = "update Product set ProState=1 where Pid=" + Pid + "";
                return SqlHelper.ExecNonquery(sql);
            }
            else
            {
                string ss = "update Product set ProState=0 where Pid=" + Pid + "";
                return SqlHelper.ExecNonquery(ss);
            }


        }
        /// <summary>
        /// 首页新鲜水果展示
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<Product> Show1()
        {
            string str = "select top 5 Product.Pid,Product.ProState,Product.Pname,Product.Specification,Product.Price,PImg.PImgHref,SecondType.Sname,FirstType.Fname from Product join PImg on PImg.Pid=Product.Pid join SecondType on SecondType.Sid=Product.Sid join FirstType on FirstType.Fid=SecondType.Fid where FirstType.Fname='新鲜水果' and Pzhu=1 and Product.ProState=1";
            DataTable dt = SqlHelper.ExecDataTable(str);
            List<Product> list = new List<Product>();
            foreach (DataRow d in dt.Rows)
            {
                Product p = new Product();
                p.Pid = Convert.ToInt32(d["Pid"]);
                p.Specification = d["Specification"].ToString();
                p.Pname = d["Pname"].ToString();
                p.Price = Convert.ToDecimal(d["Price"]);
                p.PImgHref = d["PImgHref"].ToString();
                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 根据编号获取商品信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Product GetProductId(int id)
        {
            string str = "select * from Product where Product.Pid=" + id + "";
            SqlDataReader reader = SqlHelper.ExecReader(str);
            Product p = new Product();
            while (reader.Read())
            {
                p.Pid = Convert.ToInt32(reader["Pid"]);
                p.Pname = reader["Pname"].ToString();
                p.Price = Convert.ToDecimal(reader["Price"]);
                p.Specification = reader["Specification"].ToString();
                p.Explain = reader["Explain"].ToString();
                p.Place = reader["Place"].ToString();
            }
            return p;
        }
        /// <summary>
        /// 首页新鲜蔬菜展示
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<Product> ShowShucai()
        {
            string str = "select top 5 Product.Pid,Product.Pname,Product.Specification,Product.Price,PImg.PImgHref,SecondType.Sname,FirstType.Fname from Product join PImg on PImg.Pid=Product.Pid join SecondType on SecondType.Sid=Product.Sid join FirstType on FirstType.Fid=SecondType.Fid where FirstType.Fname='新鲜蔬菜' and Pzhu=1 and Product.ProState=1";
            DataTable dt = SqlHelper.ExecDataTable(str);
            List<Product> list = new List<Product>();
            foreach (DataRow d in dt.Rows)
            {
                Product p = new Product();
                p.Pid = Convert.ToInt32(d["Pid"]);
                p.Pname = d["Pname"].ToString();
                p.Price = Convert.ToDecimal(d["Price"]);
                p.PImgHref = d["PImgHref"].ToString();
                p.Specification = d["Specification"].ToString();
                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 首页水产海鲜展示
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<Product> Showshuichan()
        {
            string str = "select top 5 Product.Pid,Product.Pname,Product.Specification,Product.Price,PImg.PImgHref,SecondType.Sname,FirstType.Fname from Product join PImg on PImg.Pid=Product.Pid join SecondType on SecondType.Sid=Product.Sid join FirstType on FirstType.Fid=SecondType.Fid where FirstType.Fname='水产海鲜' and Pzhu=1 and Product.ProState=1";
            DataTable dt = SqlHelper.ExecDataTable(str);
            List<Product> list = new List<Product>();
            foreach (DataRow d in dt.Rows)
            {
                Product p = new Product();
                p.Pid = Convert.ToInt32(d["Pid"]);
                p.Pname = d["Pname"].ToString();
                p.Price = Convert.ToDecimal(d["Price"]);
                p.PImgHref = d["PImgHref"].ToString();
                p.Specification = d["Specification"].ToString();
                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 根据一级类别查询商品
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<Product> GetFirsTypeNameProduct(string name)
        {
            string str = "select top 6 Product.Pid,Product.CPrice,Product.Pname,Product.CPrice,Product.CPrice,Product.Specification,Product.Price,PImg.PImgHref from Product join PImg on PImg.Pid=Product.Pid join SecondType on SecondType.Sid=Product.Sid join FirstType on FirstType.Fid=SecondType.Fid where FirstType.Fname='" + name + "' and Pzhu=1 and Product.ProState=1";
            DataTable dt = SqlHelper.ExecDataTable(str);
            List<Product> list = new List<Product>();
            foreach (DataRow d in dt.Rows)
            {
                Product p = new Product();
                p.Pid = Convert.ToInt32(d["Pid"]);
                p.Pname = d["Pname"].ToString();
                p.CPrice = Convert.ToDecimal(d["CPrice"]);
                p.Price = Convert.ToDecimal(d["Price"]);
                p.PImgHref = d["PImgHref"].ToString();
                p.Specification = d["Specification"].ToString();
                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 爆款推荐
        /// </summary>
        /// <returns></returns>
        public List<Product> ShowBaoKuan()
        {
            string str = "select top 10 product.Pid,Product.CPrice,Product.Pname,Product.Specification,Product.Price,PImg.PImgHref,SecondType.Sname,FirstType.Fname from Product join PImg on PImg.Pid=Product.Pid join SecondType on SecondType.Sid=Product.Sid join FirstType on FirstType.Fid=SecondType.Fid where FirstType.Fname='爆款推荐' and Pzhu=1 and Product.ProState=1";
            DataTable dt = SqlHelper.ExecDataTable(str);
            List<Product> list = new List<Product>();
            foreach (DataRow d in dt.Rows)
            {
                Product p = new Product();
                p.Pid = Convert.ToInt32(d["Pid"]);
                p.Pname = d["Pname"].ToString();
                p.CPrice = Convert.ToDecimal(d["CPrice"]);
                p.Price = Convert.ToDecimal(d["Price"]);
                p.PImgHref = d["PImgHref"].ToString();
                p.Specification = d["Specification"].ToString();
                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 显示肉禽食品
        /// </summary>
        /// <returns></returns>
        public List<Product> ShowRou()
        {
            string str = "select top 10 product.Pid,Product.CPrice,Product.Pname,Product.Specification,Product.Price,PImg.PImgHref,SecondType.Sname,FirstType.Fname from Product join PImg on PImg.Pid=Product.Pid join SecondType on SecondType.Sid=Product.Sid join FirstType on FirstType.Fid=SecondType.Fid where FirstType.Fname='肉禽食品' and Pzhu=1 and Product.ProState=1";
            DataTable dt = SqlHelper.ExecDataTable(str);
            List<Product> list = new List<Product>();
            foreach (DataRow d in dt.Rows)
            {
                Product p = new Product();
                p.Pid = Convert.ToInt32(d["Pid"]);
                p.Pname = d["Pname"].ToString();
                p.CPrice = Convert.ToDecimal(d["CPrice"]);
                p.Price = Convert.ToDecimal(d["Price"]);
                p.PImgHref = d["PImgHref"].ToString();
                p.Specification = d["Specification"].ToString();
                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 显示水产海鲜
        /// </summary>
        /// <returns></returns>
        public List<Product> ShowShui()
        {
            string str = "select top 10 product.Pid,Product.CPrice,Product.Pname,Product.Specification,Product.Price,PImg.PImgHref,SecondType.Sname,FirstType.Fname from Product join PImg on PImg.Pid=Product.Pid join SecondType on SecondType.Sid=Product.Sid join FirstType on FirstType.Fid=SecondType.Fid where FirstType.Fname='水产海鲜' and Pzhu=1 and Product.ProState=1";
            DataTable dt = SqlHelper.ExecDataTable(str);
            List<Product> list = new List<Product>();
            foreach (DataRow d in dt.Rows)
            {
                Product p = new Product();
                p.Pid = Convert.ToInt32(d["Pid"]);
                p.Pname = d["Pname"].ToString();
                p.CPrice = Convert.ToDecimal(d["CPrice"]);
                p.Price = Convert.ToDecimal(d["Price"]);
                p.PImgHref = d["PImgHref"].ToString();
                p.Specification = d["Specification"].ToString();
                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 显示厨房用品
        /// </summary>
        /// <returns></returns>
        public List<Product> ShowChu()
        {
            string str = "select top 10 product.Pid,Product.CPrice,Product.Pname,Product.Specification,Product.Price,PImg.PImgHref,SecondType.Sname,FirstType.Fname from Product join PImg on PImg.Pid=Product.Pid join SecondType on SecondType.Sid=Product.Sid join FirstType on FirstType.Fid=SecondType.Fid where FirstType.Fname='厨房用品' and Pzhu=1 and Product.ProState=1";
            DataTable dt = SqlHelper.ExecDataTable(str);
            List<Product> list = new List<Product>();
            foreach (DataRow d in dt.Rows)
            {
                Product p = new Product();
                p.Pid = Convert.ToInt32(d["Pid"]);
                p.Pname = d["Pname"].ToString();
                p.CPrice = Convert.ToDecimal(d["CPrice"]);
                p.Price = Convert.ToDecimal(d["Price"]);
                p.PImgHref = d["PImgHref"].ToString();
                p.Specification = d["Specification"].ToString();
                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 显示小型家电
        /// </summary>
        /// <returns></returns>
        public List<Product> ShowJia()
        {
            string str = "select top 10 product.Pid,Product.CPrice,Product.Pname,Product.Specification,Product.Price,PImg.PImgHref,SecondType.Sname,FirstType.Fname from Product join PImg on PImg.Pid=Product.Pid join SecondType on SecondType.Sid=Product.Sid join FirstType on FirstType.Fid=SecondType.Fid where FirstType.Fname='小型家电' and Pzhu=1 and Product.ProState=1";
            DataTable dt = SqlHelper.ExecDataTable(str);
            List<Product> list = new List<Product>();
            foreach (DataRow d in dt.Rows)
            {
                Product p = new Product();
                p.Pid = Convert.ToInt32(d["Pid"]);
                p.Pname = d["Pname"].ToString();
                p.CPrice = Convert.ToDecimal(d["CPrice"]);
                p.Price = Convert.ToDecimal(d["Price"]);
                p.PImgHref = d["PImgHref"].ToString();
                p.Specification = d["Specification"].ToString();
                list.Add(p);
            }
            return list;
        }

        /// <summary>
        /// 分页展示商品列表页
        /// </summary>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">每页显示的记录</param>
        /// <param name="pname">商品名称</param>
        /// <returns></returns>
        public ProductPage ShowProductPage(int pageIndex,int pageSize,string pname)
        {

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@pageindex",SqlDbType.Int),
                new SqlParameter("@pagesize",SqlDbType.Int),
                new SqlParameter("@count",SqlDbType.Int),
                new SqlParameter("@pname",SqlDbType.VarChar,20)
            };
            parameter[0].Value = pageIndex;
            parameter[1].Value = pageSize;
            parameter[2].Direction = ParameterDirection.Output;
            parameter[3].Value = pname;

            DataTable table=SqlHelper.ExecDataTable_Proc("pro_productPage",parameter);

            int i = int.Parse(parameter[2].Value.ToString());//存储过程的返回值

            List<Product> list = new List<Product>();
            foreach (DataRow item in table.Rows)
            {
                
                Product pro = new Product();
                pro.Pid=Convert.ToInt32(item["Pid"]);
                pro.Pname = item["Pname"].ToString() ;
                pro.Price = Convert.ToDecimal(item["Price"]);
                pro.Specification = item["Specification"].ToString();
                pro.Explain = item["Explain"].ToString();
                pro.Place = item["Place"].ToString();
                pro.PImgHref = item["PImgHref"].ToString();
                list.Add(pro);
            
            }

            ProductPage page = new ProductPage();
            page.index = pageIndex;
            page.size = pageSize;
            page.count = i;
            page.product = list;

            return page;
        }
        /// <summary>
        /// 显示4肉禽食品
        /// </summary>
        /// <returns></returns>
        public List<Product> ShowRou2()
        {
            string str = "select top 4 product.Pid,Product.CPrice,Product.Pname,Product.Specification,Product.Price,PImg.PImgHref,SecondType.Sname,FirstType.Fname from Product join PImg on PImg.Pid=Product.Pid join SecondType on SecondType.Sid=Product.Sid join FirstType on FirstType.Fid=SecondType.Fid where FirstType.Fname='肉禽食品' and Pzhu=1 and Product.ProState=1";
            DataTable dt = SqlHelper.ExecDataTable(str);
            List<Product> list = new List<Product>();
            foreach (DataRow d in dt.Rows)
            {
                Product p = new Product();
                p.Pid = Convert.ToInt32(d["Pid"]);
                p.Pname = d["Pname"].ToString();
                p.CPrice = Convert.ToDecimal(d["CPrice"]);
                p.Price = Convert.ToDecimal(d["Price"]);
                p.PImgHref = d["PImgHref"].ToString();
                p.Specification = d["Specification"].ToString();
                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 显示4水产海鲜
        /// </summary>
        /// <returns></returns>
        public List<Product> ShowShui2()
        {
            string str = "select top 4 product.Pid,Product.CPrice,Product.Pname,Product.Specification,Product.Price,PImg.PImgHref,SecondType.Sname,FirstType.Fname from Product join PImg on PImg.Pid=Product.Pid join SecondType on SecondType.Sid=Product.Sid join FirstType on FirstType.Fid=SecondType.Fid where FirstType.Fname='水产海鲜' and Pzhu=1 and Product.ProState=1";
            DataTable dt = SqlHelper.ExecDataTable(str);
            List<Product> list = new List<Product>();
            foreach (DataRow d in dt.Rows)
            {
                Product p = new Product();
                p.Pid = Convert.ToInt32(d["Pid"]);
                p.Pname = d["Pname"].ToString();
                p.CPrice = Convert.ToDecimal(d["CPrice"]);
                p.Price = Convert.ToDecimal(d["Price"]);
                p.PImgHref = d["PImgHref"].ToString();
                p.Specification = d["Specification"].ToString();
                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 显示4爆款推荐
        /// </summary>
        /// <returns></returns>
        public List<Product> ShowbaokuanT()
        {
            string str = "select top 4 product.Pid,Product.CPrice,Product.Pname,Product.Specification,Product.Price,PImg.PImgHref,SecondType.Sname,FirstType.Fname from Product join PImg on PImg.Pid=Product.Pid join SecondType on SecondType.Sid=Product.Sid join FirstType on FirstType.Fid=SecondType.Fid where FirstType.Fname='爆款推荐' and Pzhu=1 and Product.ProState=1";
            DataTable dt = SqlHelper.ExecDataTable(str);
            List<Product> list = new List<Product>();
            foreach (DataRow d in dt.Rows)
            {
                Product p = new Product();
                p.Pid = Convert.ToInt32(d["Pid"]);
                p.Pname = d["Pname"].ToString();
                p.CPrice = Convert.ToDecimal(d["CPrice"]);
                p.Price = Convert.ToDecimal(d["Price"]);
                p.PImgHref = d["PImgHref"].ToString();
                p.Specification = d["Specification"].ToString();
                list.Add(p);
            }
            return list;
        }
    }
}
